using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using DTO;
using System.Data;
namespace DAOLayer
{
    public class DauSachDAO:AbStractDao
    {
        public DauSachDAO()
        {
            this.connect();
        }
        
        public static DataTable getListDauSach()
        {
            string query = "SELECT * FROM DauSach";
            DauSachDAO dgDao = new DauSachDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();           
            return dt;
        }
        public DataTable getListDauSach(int maTuaSach)
        {
            string query = "SELECT * FROM DauSach WHERE maTuaSach="+maTuaSach;
            DauSachDAO dgDao = new DauSachDAO();
            DataTable dt = dgDao.fillDataTable(query);
            dgDao.disconnect();

            return dt;
        }

        public DataTable getBia(int maTuaSach, String ngonNgu)
        {
            string query = "select * from DauSach where maTuaSach=" + maTuaSach + " and ngonNgu='" + ngonNgu + "'";
            DauSachDAO dausachDAO = new DauSachDAO();
            DataTable dt = dausachDAO.fillDataTable(query);
            dausachDAO.disconnect();

            return dt;
        }

        public int getMaDauSach(int maTuaSach, String ngonNgu, String bia)
        {
            int kq = 0;
            string query = "select * from DauSach where maTuaSach=" + maTuaSach + " and ngonNgu='" + ngonNgu + "' and bia='" + bia +"'";

            IDataReader dr = this.executeQuery(query);
            if (dr.Read())
                kq = int.Parse(dr[0].ToString().Trim());
            return kq;

           // DauSachDAO dausachDAO = new DauSachDAO();
           //  DataTable dt = dausachDAO.fillDataTable(query);
           // dausachDAO.disconnect();

           // return dt;
        }

        public DauSachDTO getDauSach(int maDauSach)
        {
            string strQuery = "select * from DauSach where maDauSach= " + maDauSach;
            DauSachDAO dgDao = new DauSachDAO();
            IDataReader dr = dgDao.executeQuery(strQuery);
            DauSachDTO dgDto = new DauSachDTO();
            if (dr.Read())
            {
                dgDto.MaDauSach = dr.GetInt32(0);
                dgDto.MaTuaSach = dr.GetInt32(1);
                dgDto.NgonNgu = dr.GetString(2);
                dgDto.Bia = dr.GetString(3);
                dgDto.TrangThai = dr.GetString(4);
            }
            dgDao.disconnect();
            return dgDto;
        }
        public int LayMaDauSach(string ngonngu)
        {
            int kq=0;
            string strSQL = "select * from DauSach where ngonNgu= '" + ngonngu + "'";
            IDataReader dr = this.executeQuery(strSQL);
            if (dr.Read())
                kq = int.Parse(dr[0].ToString().Trim());
            return kq;

        }
        public int LayMaTuaSach(string ngonngu)
        {
            int kq = 0;
            string strSQL = "select * from DauSach where ngonNgu= '" + ngonngu + "'";
            IDataReader dr = this.executeQuery(strSQL);
            if (dr.Read())
                kq = int.Parse(dr[1].ToString().Trim());
            return kq;

        }
        public bool insert(ref DauSachDTO info)
        {
            string insertCommand = "INSERT INTO DauSach(maTuaSach,ngonNgu,bia,trangThai) VALUES(" +
                info.MaTuaSach+",'"+
                info.NgonNgu + "','" +
                info.Bia + "','" +
                info.TrangThai + "')";
            bool flag = executeNonQuery(insertCommand);     
            return flag;
            }
        public bool update(DauSachDTO info)
        {
            string updateCommand = "UPDATE DauSach " +
                                    "SET maTuaSach="+info.MaTuaSach+", ngonNgu = '" + info.NgonNgu + "', bia='"+info.Bia+ "', trangThai='" + info.TrangThai +
              
                      
                                    "' WHERE maDauSach = " + info.MaDauSach ;
            bool flag = this.executeNonQuery(updateCommand);
    
            return flag;
        }
        public bool delete(int maDauSach)
        {   
            CuonSachDAO cuonsachDAO = new CuonSachDAO();
            if(cuonsachDAO.kiemTraTonTai(maDauSach))
                return false;
            string deleteCommand = "DELETE FROM DauSach WHERE maDauSach = " + maDauSach;
            bool flag = this.executeNonQuery(deleteCommand);
            return flag;
        }
        public bool kiemTraTonTai(int matuasach)
        {
            string query = "select count(*) from DauSach where maTuaSach = " + matuasach;
            int kt = (int)this.executeScalar(query);
            if (kt == 1)
                return true;
            else
                return false;
        }
        public bool deleteTheoTuaSach(int maTuaSach)
        {
            string deleteCommand = "DELETE FROM DauSach WHERE maTuaSach = " + maTuaSach;
            bool flag = this.executeNonQuery(deleteCommand);
            return flag;
        }
    }
}